﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Aspose.Cells;
using System.Data;
using System.Collections;
using System.Drawing;
namespace cebao.Web
{
    public class OutExcel
    {
/// <summary>
		/// 利用Aspose.Cells对DataTable数据生成Excel文件
		/// </summary>
		/// <returns>是否成功</returns>
		/// <param name="response">包含存储路径，直接用Response即可</param>
		/// <param name="dt">数据体</param>
		/// <param name="FileName">文件名</param>
		/// <param name="SheetName">sheet名</param>
		/// <param name="Title">表头</param>
		/// <param name="ColTitle">列标题，字符串数组</param>
		/// <param name="ColName">列名，字符串数组</param>
		/// <param name="ColWidth">列宽，整数数组</param>
		/// <param name="ColStyle">列样式，整数数组，1=居中文本、2=特殊字体Georgia、3=居左文本、4=整数(x)、5=2位小数(x,xxx.xx)、6=日期(yyyy-M-d不带时间)、7=百分数(0.123=12.3%)</param>
		/// <param name="ColTitleRow">列标题的开始行号，有title的设为1即可</param>
		/// <param name="err">返回的错误信息</param>
		public static bool ExportToExcel(System.Web.HttpResponse response,DataTable dt,string hnames,string outfile,DateTime st,DateTime et, string anames,ref string err)
		{			
			try
			{
				Workbook workbook = new Workbook(); 

							string path = System.Web.HttpContext.Current.Server.MapPath("~");
							path = path.Substring(0, path.LastIndexOf("\\"));		
							path += @"\template\mb.xls";				
							workbook.Open(path);
				//打开sheet
				//workbook.Worksheets.Clear();
				Worksheet worksheet = workbook.Worksheets[0];

				Cells cells = worksheet.Cells; 	
			    string[] hs=hnames.Split(',');
                int count = 0;
                DataView dtv;
                int currentRow = 4;
                int currentcol = 1;
                DateTime cst = st;
                //2015年**自动虫情测报灯监测总记录表																																
                cells[0, 0].PutValue(st.Year + "年" + anames + "自动虫情测报灯监测总记录表");
                while (cst <= et)
                {

                    cells[currentRow, 0].PutValue(cst.ToString("MM月dd日"));
                    currentRow++;
                    cst=cst.AddDays(1);
                }
                currentRow = 4;
                DateTime rdt;
                int days = 0;
                foreach (string hname in hs)
                {
                    count = 0;
                    dtv = dt.DefaultView;
                    dtv.RowFilter = "hname='"+hname+"'";
                    cells[2, currentcol].PutValue(hname);
                    cst = st.AddDays(-1);
                    foreach (DataRowView dv in dtv)
                    {
                        rdt = DateTime.Parse(dv["inputTime"].ToString());
                        //数据前的0
                        days =(int)(rdt - cst).TotalDays;
                        cst = rdt;
                        while (days > 1)
                        {
                            cells[currentRow, currentcol].PutValue("0");
                            days--;
                            currentRow++;
                        }
                        int rcount =0;
                        int.TryParse(dv["allsum"].ToString(), out rcount);
                        cells[currentRow, currentcol].PutValue(rcount.ToString());
                        currentRow++;
                        count += rcount;

                    }
                    //填写最后的行内的0
                    days = (int)(et - cst).TotalDays;
                    while (days >0)
                    {
                        cells[currentRow, currentcol].PutValue("0");
                        days--;
                        currentRow++;
                    }
                    //总数
                    cells[3, currentcol].PutValue(count.ToString());
                    currentRow=4;
                    currentcol++;
                }
                workbook.Save(outfile, FileFormatType.Default, SaveType.OpenInExcel, response);
				return true;
			}
			catch (Exception ex)
			{
				err = ex.Message;
				return false;
			}
		}//end ExportToExcel()
        
    }
}